﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace NetSysData
{
	public class NetSysData
	{

		private NETSYSEntities nsEntities;

		public NetSysData()
		{
			nsEntities = new NETSYSEntities();
		}

		private string GetApplicantKey()
		{
			return Guid.NewGuid().ToString();
		}

		public string AddApplicant(APPLICANT newApplicant)
		{
			newApplicant.APPLICANT_KEY = GetApplicantKey();
			nsEntities.APPLICANTs.AddObject(newApplicant);
			nsEntities.SaveChanges();

			return newApplicant.APPLICANT_KEY;
		}

		public void AddPosition(POSITION newPosition)
		{
			nsEntities.POSITIONs.AddObject(newPosition);
			nsEntities.SaveChanges();
		}

		public void AddQuestion(QUESTION newQuestion)
		{
			nsEntities.QUESTIONs.AddObject(newQuestion);
			nsEntities.SaveChanges();
		}

		public POSITION[] GetPositions()
		{
			var positions = from p in nsEntities.POSITIONs select p;
			return positions.ToArray();
		}

		public QUESTION_LEVEL[] GetQuestionLevels()
		{
			var levels = from l in nsEntities.QUESTION_LEVEL orderby l.LEVEL_ID select l;
			return levels.ToArray();
		}

		public QUESTION_CATEGORY[] GetQuestionCagetories()
		{
			var categories = from c in nsEntities.QUESTION_CATEGORY orderby c.CATEGORY_NAME select c;
			return categories.ToArray();
		}

		public int ValidateLogin(string email, string key)
		{
			var applicantEntry = from a in nsEntities.APPLICANTs where a.EMAIL == email && a.APPLICANT_KEY == key select a;
			if (applicantEntry.Count() == 1)
			{
				return applicantEntry.Single().APPLICANT_ID;
			}
			else
			{
				return -1;
			}
		}

		public QUIZ[] GetQuizzes()
		{
			var quizzes = from q in nsEntities.QUIZs select q;
			return quizzes.ToArray();
		}

		public QUIZ GetQuiz(int quiz_id)
		{
			var quiz = from q in nsEntities.QUIZs where q.QUIZ_ID == quiz_id select q;
			try
			{
				return quiz.Single();
			}
			catch (Exception)
			{
				return null;
			}
		}

		public QUESTION GetQuestion(int question_id)
		{
			var question = from q in nsEntities.QUESTIONs where q.QUESTION_ID == question_id select q;
			try
			{
				return question.Single();
			}
			catch (Exception)
			{
				return null;
			}
		}

		public QUIZ_QUESTION[] GetQuizQuestions(int quiz_id)
		{
			var questions = from q in nsEntities.QUIZ_QUESTION where q.QUIZ_ID == quiz_id select q;
			return questions.ToArray();
		}

		public GetApplicants_Result[] GetApplicants()
		{
			return nsEntities.GetApplicants().ToArray();
		}

		public QUESTION[] GetQuestions()
		{
			var questions = from q in nsEntities.QUESTIONs orderby q.QUESTION_ID select q;
			return questions.ToArray();
		}

		public void AddQuiz(QUIZ newQuiz)
		{
			nsEntities.QUIZs.AddObject(newQuiz);
			nsEntities.SaveChanges();
		}

		public void Save()
		{
			nsEntities.SaveChanges();
		}

		public GetQuizList_Result[] GetQuizList()
		{
			return nsEntities.GetQuizList().ToArray();
		}

		public GetPositionList_Result[] GetPositionList()
		{
			return nsEntities.GetPositionList().ToArray();
		}

		public void DeleteApplicant(int applicant_id)
		{
			var applicant = from a in nsEntities.APPLICANTs where a.APPLICANT_ID == applicant_id select a;
			if (applicant.Count() == 1)
			{
				APPLICANT a = applicant.Single();
				nsEntities.APPLICANTs.DeleteObject(a);
				nsEntities.SaveChanges();
			}
		}

		public APPLICANT[] GetUpcomingInterviews()
		{
			DateTime futureDate = DateTime.Now.AddDays(30);
			var applicants = from a in nsEntities.APPLICANTs where a.INTERVIEW_DATE < futureDate orderby a.INTERVIEW_DATE select a;
			return applicants.ToArray();
		}

		public int DeleteQuiz(int quiz_id)
		{
			var quiz = from q in nsEntities.QUIZs where q.QUIZ_ID == quiz_id select q;

			if (quiz.Count() == 1)
			{
				QUIZ q = quiz.Single();

				if (q.POSITIONs.Count() > 0)
				{
					return -2;
				}

				nsEntities.QUIZs.DeleteObject(q);
				nsEntities.SaveChanges();
				return 0;
			}
			return -1;
		}

		public int DeleteAttempt(int attempt_id)
		{
			var attempt = from a in nsEntities.QUIZ_ATTEMPT where a.ATTEMPT_ID == attempt_id select a;
			if (attempt.Count() == 1)
			{
				QUIZ_ATTEMPT a = attempt.Single();
				nsEntities.QUIZ_ATTEMPT.DeleteObject(a);
				nsEntities.SaveChanges();
				return 0;
			}
			return -1;
		}

		public int DeletePosition(int position_id)
		{
			var position = from p in nsEntities.POSITIONs where p.POSITION_ID == position_id select p;
			if (position.Count() == 1)
			{
				POSITION p = position.Single();
				if (p.APPLICANTs.Count() > 0)
				{
					return -2;
				}

				nsEntities.POSITIONs.DeleteObject(p);
				nsEntities.SaveChanges();
				return 0;
			}
			return -1;
		}

		public GetQuestionList_Result[] GetQuestionList()
		{
			return nsEntities.GetQuestionList().ToArray();
		}

		public int DeleteQuestion(int question_id)
		{
			var question = from q in nsEntities.QUESTIONs where q.QUESTION_ID == question_id select q;
			if (question.Count() == 1)
			{
				QUESTION q = question.Single();
				if (q.QUIZ_QUESTION.Count() > 0)
				{
					return -2;
				}

				nsEntities.QUESTIONs.DeleteObject(q);
				nsEntities.SaveChanges();
				return 0;
			}
			return -1;
		}

		public GetAttemptList_Result[] GetAttemptList()
		{
			return nsEntities.GetAttemptList().ToArray();
		}

		public GetRecentAttempts_Result[] GetRecentAttempts()
		{
			return nsEntities.GetRecentAttempts().ToArray();
		}

		public void SaveQuestion(QUESTION q)
		{
			// First clear all answers
			var answers = from a in nsEntities.ANSWERs where a.QUESTION_ID == q.QUESTION_ID select a;
			foreach (ANSWER a in answers.ToArray())
			{
				nsEntities.ANSWERs.DeleteObject(a);
			}
			nsEntities.SaveChanges();
		}

		// Quiz-taking functions
		public int StartQuiz(int applicant_id)
		{
			System.Data.Objects.ObjectParameter attempt_id = new System.Data.Objects.ObjectParameter("ATTEMPT_ID", typeof(int));

			nsEntities.StartQuiz(applicant_id, attempt_id);
			return (int)attempt_id.Value;
		}

		public int GetAttemptNextQuestion(int attempt_id)
		{
			return (int)nsEntities.GetAttemptNextQuestion(attempt_id).Single().NEXT_QUESTION_ID;
		}

		public void AnswerQuestionSA(int attempt_id, int question_id, string sa_text)
		{
			QUESTION_RESULT qr = new QUESTION_RESULT();
			qr.ATTEMPT_ID = attempt_id;
			qr.QUESTION_ID = question_id;
			// Answered incorrectly until graded.
			qr.ANSWERED_CORRECTLY = -1;
			nsEntities.QUESTION_RESULT.AddObject(qr);

			SHORT_ANSWER sa = new SHORT_ANSWER();
			sa.QUESTION_ID = question_id;
			sa.ATTEMPT_ID = attempt_id;
			sa.ANSWER_TEXT = sa_text;
			nsEntities.SHORT_ANSWER.AddObject(sa);

			nsEntities.SaveChanges();

		}

		public void AnswerQuestionMC(int attempt_id, int question_id, int answer_id)
		{
			QUESTION_RESULT qr = new QUESTION_RESULT();
			qr.ATTEMPT_ID = attempt_id;
			qr.QUESTION_ID = question_id;

			var answer = from a in nsEntities.ANSWERs where a.QUESTION_ID == question_id && a.IS_CORRECT_ANSWER == 1 select a;
			qr.ANSWERED_CORRECTLY = (answer.Single().ANSWER_ID == answer_id) ? 1 : 0;

			nsEntities.QUESTION_RESULT.AddObject(qr);
			nsEntities.SaveChanges();

		}

		public QUIZ_ATTEMPT GetQuizAttempt(int attempt_id)
		{
			var attempt = from a in nsEntities.QUIZ_ATTEMPT where a.ATTEMPT_ID == attempt_id select a;
			if (attempt.Count() == 1)
			{
				return attempt.Single();
			}
			else
			{
				return null;
			}
		}

		public GetAttemptDetail_Result[] GetQuizAttemptDetail(int attempt_id)
		{
			return nsEntities.GetAttemptDetail(attempt_id).ToArray();
		}

		public int UpdateAttemptQuestion(int attempt_id, int question_id, int answered_correctly)
		{
			var qr = from q in nsEntities.QUESTION_RESULT where q.ATTEMPT_ID == attempt_id && q.QUESTION_ID == question_id select q;
			if (qr.Count() == 1)
			{
				QUESTION_RESULT result = qr.Single();
				result.ANSWERED_CORRECTLY = answered_correctly;
				nsEntities.SaveChanges();
				return 0;
			}
			return -1;
		}

		public void SetQuizGraded(int attempt_id)
		{
			var attempt = from a in nsEntities.QUIZ_ATTEMPT where a.ATTEMPT_ID == attempt_id select a;
			if (attempt.Count() == 1)
			{
				attempt.Single().GRADED = 1;
				nsEntities.SaveChanges();
			}
		}

		public GetApplicantLevelStatistics_Result[] GetApplicantLevelStatics(int attempt_id)
		{
			QUESTION_LEVEL[] levels = GetQuestionLevels();
			int num_levels = levels.Count();
			GetApplicantLevelStatistics_Result[] result = new GetApplicantLevelStatistics_Result[num_levels];
			int i = 0;

			foreach (QUESTION_LEVEL ql in levels)
			{
				try
				{
					result[i] = nsEntities.GetApplicantLevelStatistics(attempt_id, ql.LEVEL_ID).Single();
				}
				catch (Exception)
				{
					result[i] = null;
				}
				i++;
			}
			return result;
		}

		public GetQuizLevelStatistics_Result[] GetQuizLevelStatistics(int attempt_id)
		{
			QUESTION_LEVEL[] levels = GetQuestionLevels();
			int num_levels = levels.Count();
			GetQuizLevelStatistics_Result[] result = new GetQuizLevelStatistics_Result[num_levels];
			int i = 0;

			foreach (QUESTION_LEVEL ql in levels)
			{
				try
				{
					result[i] = nsEntities.GetQuizLevelStatistics(attempt_id, ql.LEVEL_ID).Single();
				}
				catch (Exception)
				{
					result[i] = null;
				}
				i++;
			}
			return result;
		}

		public GetApplicantCategoryStatistics_Result[] GetApplicantCategoryStatistics(int attempt_id)
		{
			QUESTION_CATEGORY[] categories = GetQuestionCagetories();
			int num_categories = categories.Count();
			GetApplicantCategoryStatistics_Result[] result = new GetApplicantCategoryStatistics_Result[num_categories];
			int i = 0;

			foreach (QUESTION_CATEGORY category in categories)
			{
				try
				{
					result[i] = nsEntities.GetApplicantCategoryStatistics(attempt_id, category.CATEGORY_ID).Single();
				}
				catch (Exception)
				{
					result[i] = null;
				}
				i++;
			}
			return result;
		}

		public GetQuizCategoryStatistics_Result[] GetQuizCategoryStatistics(int attempt_id)
		{
			QUESTION_CATEGORY[] categories = GetQuestionCagetories();
			int num_categories = categories.Count();
			GetQuizCategoryStatistics_Result[] result = new GetQuizCategoryStatistics_Result[num_categories];
			int i = 0;

			foreach (QUESTION_CATEGORY category in categories)
			{
				try
				{
					result[i] = nsEntities.GetQuizCategoryStatistics(attempt_id, category.CATEGORY_ID).Single();
				}
				catch (Exception)
				{
					result[i] = null;
				}
				i++;
			}
			return result;
		}

		public int GetQuizNumQuestions(int attempt_id)
		{
			var attempt = from a in nsEntities.QUIZ_ATTEMPT where a.ATTEMPT_ID == attempt_id select a;
			if (attempt.Count() == 1)
			{
				return attempt.Single().QUIZ.QUIZ_QUESTION.Count();
			}
			return -1;
		}

		public int GetAttemptQuestionsAnswered(int attempt_id)
		{
			try
			{
				var answered = from a in nsEntities.QUESTION_RESULT where a.ATTEMPT_ID == attempt_id select a;
				return answered.Count();
			}
			catch (Exception)
			{
				return -1;
			}
		}
  
        public void addQuestionLevel(QUESTION_LEVEL newQuestionLevel)
        {
            nsEntities.QUESTION_LEVEL.AddObject(newQuestionLevel);
            nsEntities.SaveChanges();
        }



        public int DeleteQuestionLevel(int level_id)
        {
            var level = from l in nsEntities.QUESTION_LEVEL where l.LEVEL_ID == level_id select l;
            if (level.Count() == 1)
            {
                QUESTION_LEVEL l = level.Single();
                if (l.QUESTIONs.Count() > 0)
                {
                    return -2;
                }

                nsEntities.QUESTION_LEVEL.DeleteObject(l);
                nsEntities.SaveChanges();
                return 0;
            }
            return -1;
        }


        public QUESTION_LEVEL GetQuestionLevel(int level_id)
        {
            var level = from l in nsEntities.QUESTION_LEVEL where l.LEVEL_ID == level_id select l;
            try
            {
                return level.Single();
            }
            catch (Exception)
            {
                return null;
            }
        }

        public APPLICANT GetApplicant(int applicant_id)
        {
            var applicant = from a in nsEntities.APPLICANTs where a.APPLICANT_ID == applicant_id select a;
            try
            {
                return applicant.Single();
            }
            catch (Exception)
            {
                return null;
            }
        }

        public QUESTION_CATEGORY GetCategory(int cat_id)
        {
            var cat = from c in nsEntities.QUESTION_CATEGORY where c.CATEGORY_ID == cat_id select c;
            try
            {
                return cat.Single();
            }
            catch (Exception)
            {
                return null;
            }
        }
        public void addCategory(QUESTION_CATEGORY newCat)
        {
            nsEntities.QUESTION_CATEGORY.AddObject(newCat);
            nsEntities.SaveChanges();
        }

        public int DeleteCategory(int cat_id)
        {
            var cat = from c in nsEntities.QUESTION_CATEGORY where c.CATEGORY_ID == cat_id select c;
            if (cat.Count() == 1)
            {
                QUESTION_CATEGORY c = cat.Single();
                if (c.QUESTIONs.Count() > 0)
                {
                    return -2;
                }

                nsEntities.QUESTION_CATEGORY.DeleteObject(c);
                nsEntities.SaveChanges();
                return 0;
            }
            return -1;
        }


        public POSITION GetPosition(int position_id)
        {
            var position = from p in nsEntities.POSITIONs where p.POSITION_ID == position_id select p;
            if (position.Count() == 1)
            {
                return position.Single();
            }
            else
            {
                return null;
            }
        }

		  public int DeleteLevel(int level_id)
        {
            var level = from l in nsEntities.QUESTION_LEVEL where l.LEVEL_ID == level_id select l;
            if (level.Count() == 1)
            {
                QUESTION_LEVEL l = level.Single();
                if (l.QUESTIONs.Count() > 0)
                {
                    return -2;
                }

                nsEntities.QUESTION_LEVEL.DeleteObject(l);
                nsEntities.SaveChanges();
                return 0;
            }
            return -1;
        }

	}
}
